package cn.store.dao.daoImp;

import cn.store.dao.ProductDao;
import cn.store.domain.Product;
import cn.store.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class ProductDaoImp implements ProductDao {

	@Override
	public void saveProduct(Product product) throws Exception {
		String sql="INSERT INTO product VALUES(?,?,?,?,?,?,?,?,?,?)";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={product.getPid(),product.getPname(),product.getMarket_price(),product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),product.getPdesc(),product.getPflag(),product.getCid()};
		qr.update(sql,params);
	}

	@Override
	public void updateProduct(Product product) throws SQLException {
		String sql="";
		Object[] params ;
		if(product.getPimage() == null||"".equals(product.getPimage())){
			sql="UPDATE product SET pname = ? ,  market_price = ? ,  shop_price = ? , " +
					"pdate = ? ,  is_hot = ? ,  pdesc = ? ,  pflag = ? ,  cid = ?" +
					"WHERE `pid` = ? ";
			params = new Object[]{product.getPname(), product.getMarket_price(), product.getShop_price(), product.getPdate(), product.getIs_hot(), product.getPdesc(), product.getPflag(), product.getCid(), product.getPid()};
		}else{
			sql="UPDATE product SET pname = ? ,  market_price = ? ,  shop_price = ? , pimage = ? , " +
					"pdate = ? ,  is_hot = ? ,  pdesc = ? ,  pflag = ? ,  cid = ?" +
					"WHERE `pid` = ? ";
			params = new Object[]{product.getPname(), product.getMarket_price(), product.getShop_price(), product.getPimage(), product.getPdate(), product.getIs_hot(), product.getPdesc(), product.getPflag(), product.getCid(), product.getPid()};
		}

		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
	}

	@Override
	public int deleteProduct(String pid) throws SQLException {
		String sql="delete from product where pid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		int i = qr.update(sql,pid);
		return i;
	}

	//根据产品名或类别等查询出产品
	@Override
	public List<Product> findAllProductsWithPage(int startIndex, int pageSize, Map<String,Object> searchWords) throws Exception {
		/*拼接搜索项*/
		StringBuffer sql= new StringBuffer("select * from product");
		boolean flag = false;
		for (Map.Entry<String,Object>  mapkey:searchWords.entrySet()) {
			if(mapkey.getValue()!=null && !"".equals(mapkey.getValue())){
				/*第一搜索条件前面补齐WHERE*/
				if(flag == false){
					sql.append(" where ");
					flag = true;
				}else {
					sql.append(" and ");
				}
				/*区别模糊搜索和精确搜索*/
				if(mapkey.getKey().equals("pname")){
					sql.append(mapkey.getKey()+" like '%");
					sql.append(mapkey.getValue()+"%' ");
				}else if(mapkey.getKey().equals("cid")){
					/*字符串类型加' '*/
					sql.append(mapkey.getKey()+"='");
					sql.append(mapkey.getValue()+"' ");
				}else{

					sql.append(mapkey.getKey()+"=");
					sql.append(mapkey.getValue()+" ");
				}

			}

		}
		sql.append(" order by pdate desc limit  ?,?");
		System.out.println(sql.toString());
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql.toString(), new BeanListHandler<Product>(Product.class),startIndex,pageSize);
	}
	//根据产品名或类别等查询出产品数量
	@Override
	public int findTotalRecords(Map<String,Object> searchWords) throws Exception {
		/*拼接搜索项*/
		StringBuffer sql= new StringBuffer("select count(*) from product");
		boolean flag = false;
		for (Map.Entry<String,Object>  mapkey:searchWords.entrySet()) {
			if(mapkey.getValue()!=null && !"".equals(mapkey.getValue())){
				/*第一搜索条件前面补齐WHERE*/
				if(flag == false){
					sql.append(" where ");
					flag = true;
				}else {
					sql.append(" and ");
				}
				/*区别模糊搜索和精确搜索*/
				if(mapkey.getKey().equals("pname")){
					sql.append(mapkey.getKey()+" like '%");
					sql.append(mapkey.getValue()+"%' ");
				}else if(mapkey.getKey().equals("cid")){
					/*字符串类型加' '*/
					sql.append(mapkey.getKey()+"='");
					sql.append(mapkey.getValue()+"' ");
				}else{
					sql.append(mapkey.getKey()+"=");
					sql.append(mapkey.getValue()+" ");
				}

			}

		}
		System.out.println(sql.toString());
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql.toString(), new ScalarHandler());
		return num.intValue();
	}

	//查询指定产品
	@Override
	public Product findProductByPid(String pid) throws Exception {
		String sql="select * from product where pid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Product>(Product.class),pid);
	}

	@Override
	public List<Product> findHots() throws Exception {
		String sql="SELECT * FROM product WHERE pflag=0 AND is_hot=1 ORDER BY pdate DESC LIMIT 0 ,12 ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class));
	}

	@Override
	public List<Product> findNews() throws Exception {
		String sql="SELECT * FROM product WHERE pflag=0 ORDER BY pdate DESC LIMIT 0 , 12 ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class));
		
	}

	//根据类别查询出产品
	@Override
	public List findProductsByCidWithPage(String cid, int startIndex, int pageSize) throws Exception {
		String sql="select * from product where cid=? limit ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class),cid,startIndex,pageSize);
	}

	//根据类别查询出产品数量
	@Override
	public int findTotalRecords(String cid) throws Exception {
		String sql="select count(*) from product where cid =?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler(),cid);
		return num.intValue();
	}

}
